package zmee.pmas.report;

import com.alibaba.druid.support.logging.Log;
import com.alibaba.druid.support.logging.LogFactory;
import com.kingdee.cosmic.ctrl.common.CtrlUtil;
import kd.bos.algo.*;
import kd.bos.algo.input.CollectionInput;
import kd.bos.dataentity.entity.DynamicObject;
import kd.bos.dataentity.entity.DynamicObjectCollection;
import kd.bos.dataentity.utils.StringUtils;
import kd.bos.db.DB;
import kd.bos.db.DBRoute;
import kd.bos.entity.report.AbstractReportListDataPlugin;
import kd.bos.entity.report.FilterInfo;
import kd.bos.entity.report.ReportQueryParam;
import kd.bos.orm.query.QCP;
import kd.bos.orm.query.QFilter;
import kd.bos.servicehelper.BusinessDataServiceHelper;
import zmee.util.DateUtil;

import java.io.File;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author yss
 * @createTime 2022-12-30 17:23
 * @description 项目执行展示报表查询
 */
public class ProExecuteShowQuery extends AbstractReportListDataPlugin {
    private static Log log =LogFactory.getLog(ProExecuteShowQuery.class);

    @Override
    public DataSet query(ReportQueryParam queryParam, Object o) throws Throwable {
        FilterInfo filter = queryParam.getFilter();
        DynamicObjectCollection org = filter.getDynamicObjectCollection("org");//组织
        DynamicObjectCollection pro = filter.getDynamicObjectCollection("pro");//项目
        DynamicObject supplier = filter.getDynamicObject("supplier");//供应商
        DynamicObjectCollection prokind = filter.getDynamicObjectCollection("prokind");//项目分类
        Date date = filter.getDate("yearandmonth");
        Calendar calendar=Calendar.getInstance();
        calendar.setTime(date);
        int tear = calendar.get(Calendar.YEAR);//年
        int march = calendar.get(Calendar.MARCH);//月
        Date begMonth = DateUtil.getBeginDayOfMonth(tear, march);//月份开始时间
        Date endMonth = DateUtil.getEndDayOfMonth(tear, march);//月份结束时间
        Date beginYear = DateUtil.getBeginDayOfYear(tear);//本年开始时间
        Date endYear = DateUtil.getEndDayOfYear(tear);//本年结束时间
        //查找期间
        QFilter qFilter=new QFilter("begindate",QCP.large_equals,begMonth)
                .and(new QFilter("enddate",QCP.less_equals,endMonth));
        //查找年份的所有期间
        QFilter total= new QFilter("enddate",QCP.less_equals,endMonth);

        //查找年份的所有期间
        QFilter qFYear=new QFilter("begindate",QCP.large_equals,beginYear)
                .and(new QFilter("enddate",QCP.less_equals,endMonth));
        DynamicObject bd_period = BusinessDataServiceHelper.loadSingleFromCache("bd_period", "id", qFilter.toArray());
        DynamicObject[] period = BusinessDataServiceHelper.load("bd_period", "id", qFYear.toArray());
        DynamicObject[] totals = BusinessDataServiceHelper.load("bd_period", "id", total.toArray());
        String perids="";
        for (int i = 0; i < period.length; i++) {
            if(!"".equals(perids) && perids!=null ){
                perids=perids+",";
            }
            perids=perids+"'" +period[i].getPkValue().toString()+"'";
        }
        String totalids="";
        for (int i = 0; i < totals.length; i++) {
            if(!"".equals(totalids) && totalids!=null ){
                totalids=totalids+",";
            }
            totalids=totalids+ "'"+totals[i].getPkValue().toString()+"'";
        }
        StringBuilder sql=new StringBuilder();
        sql.append("/*dialect*/");
        sql.append("select \n" +
                " pmas.FBILLNO, pmas.fprojectid as project,pmas.forgid org, pmct.fpartbid partbid, pro.fprojcetbugamt bugamt, pmas.ftotalamount mount, pmct.fid as pmctid, pro.fkindid did, pmct.fbillno ctbillno, \n" +
                " pmct.fbillname ctname,t1.foriginaloftaxamount originalo ,pmct.fsigndate signdate ,\n" +
                " sum(if (ter.fbillstatus='C' and ter.fperiod='"+bd_period.getPkValue()+"',pay.fthispaymentoftaxamount,0)) as monthsum, " +
                " sum(if (ter.fbillstatus='C' and ter.fperiod in ("+totalids+"), pay.fthispaymentoftaxamount,0)) as addsum,\n" +
                " sum(if (ter.fbillstatus='C' and ter.fperiod in ("+perids+"),pay.fthispaymentoftaxamount,0)) as yearsum\n" +
                " from \n" +
                " t_pmas_budget pmas \n" +
                " left join t_pmct_contract pmct on pmas.fprojectid = pmct.fprojectid \n" +
                " left join t_pmas_pro_approval pro on pro.fproid = pmas.fprojectid \n" +
                " left join t_pmct_contract_s t1 on t1.fid = pmct.fid\n" +
                " left join t_pmct_paymentregentry pay on pay.fcontractid=pmct.fid and pay.fcontproid = pmct.fprojectid\n" +
                " left join t_pmct_paymentregister ter on ter.fid=pay.fid\n" +
                "where pmas.FISVALID=1 and pmct.fbillstatus='C' and pro.fbillstatus='C'");

        sql.append(" and pmas.FYEAR >= DATE_FORMAT('"+tear+"-1-1 00:00:00','%Y-%m-%d %H:%i:%s') and pmas.FYEAR <= DATE_FORMAT('"+tear+"-12-31 23:59:59','%Y-%m-%d %H:%i:%s')");

        if(org != null && org.size()>0){
            String orgIds=ids(org);//组织ID集合
            sql.append(" and pmas.forgid in("+orgIds+")");
        }
        if(pro != null && pro.size()>0){
            String proIds=ids(pro);//组织ID集合
            sql.append(" and pmas.fprojectid in("+proIds+")");
        }
        if(supplier != null){
            sql.append(" and pmct.fpartbid="+supplier.getPkValue());
        }

        if(prokind != null && prokind.size()>0){
            String prokindids=ids(prokind);//组织ID集合
            sql.append(" and pro.fkindid in("+prokindids+")");
        }
         sql .append( " GROUP by pmas.fprojectid,pmct.fid order By pmas.fprojectid asc");
        log.info("====项目执行表sql=====:"+sql.toString());
        DataSet dataSet = DB.queryDataSet(this.getClass().getName(), new DBRoute("scm"), sql.toString());
        DataSet ds = createDs(dataSet,tear,march);

        // 在行末尾添加一行合计 按预算总金额 和 年度预算金额,合同金额,补充合同金额,预估签证金额,决算金额,本月支付金额,本年支付金额,本年到票金额,累计支付金额,累计到票金额 合计
        // project, berz, bugamt, mount, ctname, ctbillno, partbid, originalo, pactk, amountoftax, ftaxamount, finalsettle, signdate, monthpay, yearpay, yearbillamt, totalamt, totalbillamt
        DataSet finish = ds.groupBy(null).sum("bugamt").sum("mount").sum("originalo").sum("amountoftax").sum("ftaxamount").sum("finalsettle").sum("monthpay").sum("yearpay").sum("yearbillamt").sum("totalamt").sum("totalbillamt").finish();
        // 将其他字段添加进dataset
        finish = finish.addNullField("project", "ctname", "ctbillno", "partbid", "signdate", "pactk");
        finish = finish.addField("'合计'", "berz");
        // 按照原报表dataset字段，重新排序
        finish = finish.select("project", "berz", "bugamt", "mount", "ctname", "ctbillno", "partbid", "originalo", "pactk", "amountoftax", "ftaxamount", "finalsettle", "signdate", "monthpay", "yearpay", "yearbillamt", "totalamt", "totalbillamt");
        // 将报表dataset和合计行dataset合并
        return ds.union(finish);
    }

    private String ids(DynamicObjectCollection row){
        String proIds="";
        for (int i = 0; i < row.size(); i++) {
            if(!"".equals(proIds) && proIds != null){
                proIds=proIds+",";
            }
            proIds = proIds+row.get(i).getString("id");
        }
        return proIds;
    }

    /**
     *
     * @param originDs  你查出来的原始数据集合 记住要按往来单位排序的
     * @return
     */
    private DataSet createDs(DataSet originDs,int tear,int march){


        Date beginYear = DateUtil.getBeginDayOfYear(tear);//本年开始时间
        Date endYear = DateUtil.getEndDayOfYear(tear);//本年结束时间

        Collection<Object[]> coll = new ArrayList<>();//创建显示行字段
        //下面初始创建一个空的报表数据行
        RowMeta createRowMeta = getRowMeta();
        String[] fields = createRowMeta.getFieldNames();
        String preCorUnit = null;//预先 项目
        String tempCorUnit = null;//项目

        //遍历数据集
        for (Row row : originDs.copy()) {
            tempCorUnit = row.getString("project");
            BigDecimal bugamt = row.getBigDecimal("bugamt");
            BigDecimal mount = row.getBigDecimal("mount");
            String ctname = row.getString("ctname");
            String ctbillno = row.getString("ctbillno");
            String partbid = row.getString("partbid");
            String originalo=row.getString("originalo");
            Date signdate=row.getDate("signdate");
            //关键就是这里，如果预先往来单位和当前往来单位不同了，那就满足了你的前提，然后是要在前面加一行，那就多new一个Object数组就行
            if(!StringUtils.equals(preCorUnit, tempCorUnit)){
                //这一行就是你要加的那一行
                Object[] newRow = new Object[fields.length];
                newRow[0]=tempCorUnit;
                newRow[1] = "小计";
                newRow[2]=bugamt;
                newRow[3]=mount;
                coll.add(newRow);
                preCorUnit = tempCorUnit;
            }
            /**
             * 这里就对每行的字段进行一个赋值就行
             */
            Object[] tempData = new Object[fields.length];
            tempData[0] = tempCorUnit;//项目
            tempData[4] = ctname;//合同名称
            tempData[5] = ctbillno;//合同编号
            tempData[6] = partbid;//供应商
            tempData[7] = originalo;//合同金额
            tempData[8] = "";//合同执行条款
            QFilter mentqf=new QFilter("contract.billno",QCP.equals,ctbillno)
                    .and(new QFilter("billstatus",QCP.equals,"C"));
            DynamicObject[] mens = BusinessDataServiceHelper.load("pmct_outaddagreement", "addamountoftax", mentqf.toArray());
            BigDecimal amountoftax=new BigDecimal("0");
            for (DynamicObject men:mens) {
                amountoftax = amountoftax.add(men.getBigDecimal("addamountoftax"));
            }
            tempData[9] = amountoftax;//补充合同金额 9

            DynamicObject[] isions = BusinessDataServiceHelper.load("pmct_outcontractrevision", "revisionoftaxamount", mentqf.toArray());
            BigDecimal ftaxamount=new BigDecimal("0");
            for (DynamicObject ision:isions) {
                ftaxamount=ftaxamount.add(ision.getBigDecimal("revisionoftaxamount"));
            }
            tempData[10] = ftaxamount;//预估签证金额 10

            DynamicObject finalsettle = BusinessDataServiceHelper.loadSingleFromCache("pmct_outfinalsettle", "apptaxamount", mentqf.toArray());
            BigDecimal apptaxamount=new BigDecimal("0");
            if(finalsettle!=null){
                apptaxamount=apptaxamount.add(finalsettle.getBigDecimal("apptaxamount"));
            }
            tempData[11] = apptaxamount;//决算金额
            SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
            if(signdate != null) {
                tempData[12] = sdf.format(signdate);
            }
            tempData[13]=row.getBigDecimal("monthsum");//本月支付
            tempData[14]=row.getBigDecimal("yearsum");//本年支付
            tempData[16]=row.getBigDecimal("addsum");//累计支出
            BigDecimal yearbillamt=new BigDecimal("0");
            BigDecimal totalbillamt=new BigDecimal("0");
            DynamicObject[] ininvoices = BusinessDataServiceHelper.load("pmct_ininvoice", "id,invoicedate,totaloftaxamount", new QFilter[]{
                    new QFilter("contract.id", QCP.equals, row.getString("pmctid"))
                            .and(new QFilter("project.id", QCP.equals, tempCorUnit))
                            .and(new QFilter("billstatus", QCP.equals, "C"))});
            for (DynamicObject inv:ininvoices) {
                Date invDate = inv.getDate("invoicedate");
                if(endYear.getTime() >= invDate.getTime()){
                    totalbillamt=totalbillamt.add(inv.getBigDecimal("totaloftaxamount"));
                    if(beginYear.getTime() <= invDate.getTime() && endYear.getTime() >= invDate.getTime()){
                        yearbillamt=yearbillamt.add(inv.getBigDecimal("totaloftaxamount"));
                    }
                }
            }
            tempData[15]=yearbillamt;//本年到票
            tempData[17]=totalbillamt;//累计到票
            log.info("==tempData=="+tempData);
            coll.add(tempData);

        }
        log.info("==coll=="+coll);
        log.info("==collnum=="+coll.size());
        CollectionInput collectionInput = new CollectionInput(createRowMeta, coll);
        DataSet createDataSet = Algo.create(this.getClass().getName()).createDataSet(collectionInput);
        return createDataSet;
    }

    private RowMeta getRowMeta() {
        List<Field> fields = new ArrayList<>();
        fields.add(new Field("project", DataType.StringType));//项目 0
        fields.add(new Field("berz",DataType.StringType));//小计 1
        fields.add(new Field("bugamt", DataType.BigDecimalType));//预算总金额 2
        fields.add(new Field("mount", DataType.BigDecimalType));//年度预算金额 3
        fields.add(new Field("ctname", DataType.StringType));//合同名称 4
        fields.add(new Field("ctbillno", DataType.StringType));//合同编号 5
        fields.add(new Field("partbid", DataType.BigDecimalType));//供应商 6
        fields.add(new Field("originalo", DataType.BigDecimalType));//合同金额 7
        fields.add(new Field("pactk", DataType.StringType));//合同执行条款 8
        fields.add(new Field("amountoftax", DataType.BigDecimalType));//补充合同金额 9
        fields.add(new Field("ftaxamount", DataType.BigDecimalType));//预估签证金额 10
        fields.add(new Field("finalsettle", DataType.BigDecimalType));//决算金额 11
        fields.add(new Field("signdate", DataType.StringType));//签约日期 12
        fields.add(new Field("monthpay", DataType.BigDecimalType));//本月支付金额 13
        fields.add(new Field("yearpay", DataType.BigDecimalType));//本年支付金额 14
        fields.add(new Field("yearbillamt", DataType.BigDecimalType));//本年到票金额 15
        fields.add(new Field("totalamt", DataType.BigDecimalType));//累计支付金额 16
        fields.add(new Field("totalbillamt", DataType.BigDecimalType));//累计到票金额 17

        return new RowMeta((fields.toArray(new Field[0])));
    }

}
